﻿using System.Data;
using Oracle.DataAccess.Client;

namespace Generator.Selections.DataBase
{
    static class DataDictionaryCommands
    {
        private static string SelectUsersCommandText()
        {
            return "select username from all_users where username like 'MIM%' order by username";
        }
        static string SelectTablesByOwnerCommandText(string tableOwner)
        {
            return string.Format("Select table_name from all_tables where owner = '{0}' order by table_name", tableOwner);
        }
        static string SelectTablesByNameCommandText(string tableName)
        {
            return string.Format("Select table_name from all_tables where table_name like '{0}%' order by table_name", tableName);
        }
        static string SelectColumnsCommandText(string tableName)
        {
            return string.Format("Select column_name, data_type, data_length, nullable from all_tab_columns where table_name = '{0}' order by column_id", tableName);
        }
        static string SelectPkColumnsCommandText(string tableName)
        {
            return string.Format(
                @"select acc.COLUMN_NAME 
                  from all_constraints ac, all_cons_columns acc
                  where ac.table_name = '{0}'
                  and ac.CONSTRAINT_TYPE='P'
                  and ac.CONSTRAINT_NAME= acc.CONSTRAINT_NAME
                  and ac.OWNER=acc.OWNER", tableName);
        }

        static string SelectRelationsCommandText(string tableName)
        {
            return string.Format(
                @"select acp.TABLE_NAME
                from all_constraints acr, all_constraints acp
                where acp.constraint_type = 'P'
                and acr.CONSTRAINT_TYPE='R'
                and acr.table_name = '{0}'
                and acp.CONSTRAINT_NAME= acr.R_CONSTRAINT_NAME
                and acp.OWNER= acr.R_OWNER", tableName);
        }

        static internal IDbCommand GetSelectUsersCommand()
        {
            var @select = SelectUsersCommandText();
            var cmd = new OracleCommand(@select) { Connection = ConnectionHandler.Instance.Connection };
            return cmd;
        }

        static internal IDbCommand GetSelectTableByOnwerCommand(string tableOwner = null)
        {
            var @select = SelectTablesByOwnerCommandText(tableOwner);
            var cmd = new OracleCommand(@select) { Connection = ConnectionHandler.Instance.Connection };
            return cmd;
        }

        static internal IDbCommand GetSelectTableByNameCommand(string tableName)
        {
            var @select = SelectTablesByNameCommandText(tableName);
            var cmd = new OracleCommand(@select) { Connection = ConnectionHandler.Instance.Connection };
            return cmd;
        }

        static internal IDbCommand GetSelectColumnsCommand(string tableName)
        {
            var @select = SelectColumnsCommandText(tableName);
            var cmd = new OracleCommand(@select) { Connection = ConnectionHandler.Instance.Connection };
            return cmd;
        }

        static internal IDbCommand GetSelectPkColumnsCommand(string tableName)
        {
            var @select = SelectPkColumnsCommandText(tableName);
            var cmd = new OracleCommand(@select) { Connection = ConnectionHandler.Instance.Connection };
            return cmd;
        }

        static internal IDbCommand GetRelationsCommand(string tableName)
        {
            var @select = SelectRelationsCommandText(tableName);
            var cmd = new OracleCommand(@select) { Connection = ConnectionHandler.Instance.Connection };
            return cmd;
        }

    }
}
